﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DelonixRegiaSystem.Classes
{
    class DBAccount
    {
        public static int CreateAccount(account ac)
        {
            int row = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelorixConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "Insert into Account values(@accNum,@username,@password,@staffNum,@accType,@email);";
                comm.Parameters.AddWithValue("@accNum", ac.accNum);
                comm.Parameters.AddWithValue("@username", ac.username);
                comm.Parameters.AddWithValue("@password", ac.password);
                comm.Parameters.AddWithValue("@staffNum", ac.staffNum);
                comm.Parameters.AddWithValue("@accType", ac.accType);
                comm.Parameters.AddWithValue("@email", ac.email);

                row = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return row;
        }

        public static account RetrieveAccount(string username)
        {
            account ac = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelorixConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "Select * from account where username =@username";
                comm.Parameters.AddWithValue("@username", username);
                SqlDataReader dr = comm.ExecuteReader();

                if (dr.Read())
                {
                    ac = new account();
                    ac.accNum = Convert.ToInt32(dr[0]);
                    ac.username = Convert.ToString(dr[1]);
                    ac.password = Convert.ToString(dr[2]);
                    ac.staffNum = Convert.ToInt32(dr[3]);
                    ac.accType = Convert.ToString(dr[4]);
                    ac.email = Convert.ToString(dr[5]);

                }
                dr.Close();
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return ac;
        }

        public static int UpdateAccount(string username, string pass)
        {
            int rowsupdated = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelorixConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE account SET password =@pass WHERE username=@username";
                comm.Parameters.AddWithValue("@username", username);
                comm.Parameters.AddWithValue("@pass", pass);
                rowsupdated = comm.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        }
        public static staff GetStaffById(int staffNum)
        {
            staff m = null;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelorixConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM staff WHERE staffNum=@staffNum";
                comm.Parameters.AddWithValue("@staffNum", staffNum);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    m = new staff();
                    m.staffNum = (int)dr["staffNum"];
                    m.staffName = (string)dr["staffName"];
                    m.staffType = (string)dr["staffType"];
                    m.dob = dr["dob"].ToString();
                    m.bankNum = (string)dr["bankNum"];
                    m.homeAdd = (string)dr["homeAdd"];
                    m.phoneNum = (string)dr["phoneNum"];
                    m.dutyType = (string)dr["dutyType"];

                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return m;
        }
        public static List<account> RetrieveAccount()
        {
            List<account> accList = new List<account>();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelorixConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "Select * from Account";
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    account ac = new account();
                    ac.accNum = Convert.ToInt32(dr[0]);
                    ac.username = Convert.ToString(dr[1]);
                    ac.password = Convert.ToString(dr[2]);
                    ac.staffNum = Convert.ToInt32(dr[3]);
                    ac.accType = Convert.ToString(dr[4]);
                    ac.email = Convert.ToString(dr[5]);
                    accList.Add(ac);
                }
                conn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return accList;
        }
    }
}
